﻿using System;
using System.Collections.Generic;
using System.Text;
using XHD.DBUtility;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using XHD.BLL;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.Linq;
using System.Web;
using XHD.Controller;
using XHD.Common;

namespace XHD.Excel
{
    //Excel导入核心类
    public class Excel_new
    {
        private static CRM_Customer customer = new BLL.CRM_Customer();
        private Dictionary<string, Config> configs = null;//配置文件全路径和对应的配置类键值集合
        private string excelPath = "";//excel路径（可以为目录）
        private string configPath = "";//config路径（可以为目录）
        private NPOICell npoiCell = null;//Aspose.Cells封装类
        private string NewID = "0";
        private int headerIndex = 0;//列头所在行
        private Dictionary<string, Dictionary<string, string>> ctcode = new Dictionary<string, Dictionary<string, string>>();//代码表字典
        private DataTable temp_dt = new DataTable();//零时表
        private DataTable log_dt = new DataTable();//零时表
        private DataTable customerID_dt = new DataTable();
        private int success = 0;//成功条数
        private int error = 0;//失败条数
        private string errortxt = null;
        private DataSet dsArea = new DataSet();
        private DataTable dt_xhsCode = null;
        HttpContext context;
        public Excel_new()
        {

        }
        private Model.hr_employee employee;
        private string emp_id;
        private string emp_name;
        private string uid;
        private string[] log_fields = new string[] { "id", "EventType", "EventID", "EventTitle", "Log_Content", "UserID", "UserName", "IPStreet", "EventDate" };
        public Excel_new(string excelPath, string configPath, string id, HttpContext _context)
        {
            this.NewID = id;
            this.excelPath = excelPath;
            this.configPath = configPath;
            context = _context;
            ConfigHelper.Instance().ExcelPath = excelPath;
            ConfigHelper.Instance().ConfigPath = configPath;
            InitConfig();

            var userinfo = new User_info();
            employee = userinfo.GetCurrentEmpInfo(context);

            emp_id = employee.id;
            emp_name = PageValidate.InputText(employee.name, 50);
            uid = PageValidate.InputText(employee.uid, 50);
        }
        //导入操作核心方法，负责整个Excel导入
        public string Import(string sheetName = "")
        {
            if (configs.Count > 0)
            {
                string x = ExcuteImport(sheetName);
                return x;
            }
            else
            {
                throw new Exception("Config对象个数为0，无法导入！");
            }
        }
        public string ImportFromJson()
        {
            if (configs.Count > 0)
            {
                string x = ExcuteImportFromJson();
                return x;
            }
            else
            {
                throw new Exception("Config对象个数为0，无法导入！");
            }
        }
        private string ExcuteImportFromJson()
        {
            string sqlSelect = "";//代码表

            List<EntityCodeTable> codetable = configs[configPath].Codetable;

            foreach (EntityCodeTable ect in codetable)
            {
                foreach (CodeTable ct in ect.Codetable)
                {
                    sqlSelect = $"select {ct.PrimaryKey} as ctkey,{ct.ReferenceColumn} as cttext from {ct.TableName} where 1=1";
                    if (!string.IsNullOrEmpty(ct.Condition)) sqlSelect += $" and {ct.Condition}";
                    DataSet ds = DbHelperSQL.Query(sqlSelect);
                    Dictionary<string, string> dic = new Dictionary<string, string>();
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            DataRow dr = ds.Tables[0].Rows[i];
                            dic.Add(dr["cttext"].ToString(), dr["ctkey"].ToString());
                        }
                    }
                    ctcode.Add(ct.CodeName, dic);
                }

            }

            string json = File.ReadAllText(excelPath);
            JArray list = (JArray)JsonConvert.DeserializeObject(json);

            List<Entity> entities = configs[configPath].Entities;//注意每个config中不一定只有一个实体，可以配置多个，这样每个excel可以导入到多张表中
            headerIndex = configs[configPath].HeaderIndex;
            int dataIndex = configs[configPath].DataIndex;
            string tablename = entities[0].Name;
            int rowcount = list.Count;
            foreach (Entity entity in entities)
            {
                foreach (Property p in entity.Propertys)
                {
                    try
                    {
                        string columntype = p.DataType;
                        columntype = "System." + p.DataType;
                        if (columntype == "System.string") columntype = "System.String";
                        temp_dt.Columns.Add(p.ColumnName, Type.GetType(columntype));
                    }
                    catch (Exception aa)
                    {
                    }
                }
            }
            try
            {
                while (dataIndex < list.Count)
                {
                    foreach (Entity entity in entities)
                    {
                        if (entity.Propertys.Count > 0)//说明配置了Column字段
                        {
                            GetRow(list[dataIndex], entity, dataIndex, tablename);
                        }
                    }
                    dataIndex++;
                }
            }
            catch (Exception ex)
            {

            }
            //将数据插入数据库
            string connectionString = XHD.DBUtility.PubConstant.ConnectionString;
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            using (SqlBulkCopy sqlBC = new SqlBulkCopy(conn))
            {
                sqlBC.BatchSize = 1000;
                sqlBC.BulkCopyTimeout = 3600;
                sqlBC.NotifyAfter = 10000;
                // dt.Columns.Remove("TaskID");//TaskID为列名称
                //temp_dt.Rows.Remove();
                //sqlBC.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied); 阶段性完成事件呼叫

                foreach (Entity entity in entities)
                {
                    sqlBC.DestinationTableName = entity.Name;
                    if (entity.Propertys.Count > 0)//说明配置了Column字段
                    {
                        foreach (Property p in entity.Propertys)//遍历实体的所有属性
                        {
                            sqlBC.ColumnMappings.Add(p.ColumnName, p.ColumnName);
                        }
                    }
                }
                try
                {
                    sqlBC.WriteToServer(temp_dt);
                }
                catch (Exception ex)
                {
                    Controller.IO.LogManager.Add(ex.ToString());
                }
            }

            //return XHD.Common.GetGridJSON.DataTableToJSON(temp_dt);
            return $"{{\"success\":{success},\"error\":{error},\"message\":\"{errortxt}\"}}";
        }
        private void GetRow(JToken rowData, Entity entity, int dataIndex, string tablename)
        {
            DataRow dr = temp_dt.NewRow();
            string v = "";
            foreach (Property p in entity.Propertys)//遍历实体的所有属性
            {
                if (!string.IsNullOrEmpty(p.CodeKey))
                {
                    v = GetCodeTableValue(rowData, p, dataIndex);//注意如果单元格本身的值就有“'”的情况
                    if (v == "xhdresult:error")
                    {
                        error++;
                        errortxt += $"第【{dataIndex}】行【{p.HeaderText}】列在系统中找不到对应的值。";
                        return;
                    }
                }
                else//说明此属性是一个代码表字段
                {
                    v = rowData.Value<string>(p.ColumnName);
                    if (v == "")//说明单元格中没有任何值，就要考虑“默认值”和“必须”属性
                    {
                        if (GetDefaultValue(entity.Name, p.ColumnName, p.DefaultValue) != "")//说明有默认值
                            v = GetDefaultValue(entity.Name, p.ColumnName, p.DefaultValue);
                        else//如果单元格没有值并且无默认值，则检查此属性是否是必须的
                        {
                            if (!p.Required) v = "";
                            else
                            {
                                errortxt += $"列【{p.HeaderText}】不能为空。";
                                return;
                            }
                        }
                    }

                    //属性长度检查
                    if (p.DataLength != 0 && p.DataLength != 5000)
                    {
                        if (!ValidateDataLength(v, p.DataLength))
                        {
                            error++;
                            errortxt += $"列【{p.HeaderText}】中存在长度超过【{p.DataLength}】的数据。";
                            return;
                        }
                    }

                    //检查类型
                    if (p.DataType != "" && p.DataType != "string")
                    {
                        if (p.DataType == "DateTime")
                        {
                            try
                            {
                                if (!string.IsNullOrEmpty(v))
                                {
                                    System.IFormatProvider format = new System.Globalization.CultureInfo("zh-cn", true);
                                    DateTime dateTime = DateTime.Parse(v, format);
                                }
                            }
                            catch
                            {
                                error++;
                                errortxt = $"列【{p.HeaderText}】中存在非【{p.DataType}】类型数据。{v}";
                            }
                        }
                    }

                }
                if (v == "") dr[p.ColumnName] = DBNull.Value;
                else dr[p.ColumnName] = v;
            }
            if (tablename.ToLower() == "CRM_Customer_wxinfo".ToLower())
            {
                string cause = "";
                if (ExistWXData(rowData, out cause))
                {
                    error++;
                    errortxt += cause;
                }
                else
                {
                    success++;
                    temp_dt.Rows.Add(dr);
                }
            }
            else
            {
                success++;
                temp_dt.Rows.Add(dr);
            }

        }
        private bool ExistWXData(JToken rowData, out string cause)
        {
            cause = "";
            string data = rowData.Value<string>("wxid");
            CRM_Customer_wxinfo wxinfo = new CRM_Customer_wxinfo();
            if (!string.IsNullOrWhiteSpace(data))
            {
                DataSet ds1 = wxinfo.GetList($"[wxid]='{data}' ");
                if (ds1.Tables[0].Rows.Count > 0)
                {
                    cause = "微信ID： " + data + " 已经存在！";
                    return true;
                }
            }
            //data = rowData.Value<string>("user_name");
            return false;
        }
        private void ClearDataTable()
        {
            temp_dt = new DataTable(); temp_dt.Rows.Clear(); temp_dt.Columns.Clear();
        }
        private void InitLogDataTable()
        {
            log_dt = new DataTable(); log_dt.Rows.Clear(); log_dt.Columns.Clear();
            foreach (string field in log_fields) log_dt.Columns.Add(field);
        }
        //执行Excel导入
        private string ExcuteImport(string sheetName = "")
        {
            string sqlSelect = "";//代码表

            int dataIndex = 1;//数据起始行
            int tcount = dataIndex;
            int rowcount = dataIndex;

            //return excelFullName;
            //生成代码表
            List<EntityCodeTable> codetable = configs[configPath].Codetable;

            foreach (EntityCodeTable ect in codetable)
            {
                foreach (CodeTable ct in ect.Codetable)
                {
                    sqlSelect = $"select {ct.PrimaryKey} as ctkey,{ct.ReferenceColumn} as cttext from {ct.TableName} where 1=1";
                    if (!string.IsNullOrEmpty(ct.Condition)) sqlSelect += $" and {ct.Condition}";
                    DataSet ds = DbHelperSQL.Query(sqlSelect);
                    Dictionary<string, string> dic = new Dictionary<string, string>();
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            DataRow dr = ds.Tables[0].Rows[i];
                            dic.Add(dr["cttext"].ToString(), dr["ctkey"].ToString());
                        }
                    }
                    ctcode.Add(ct.CodeName, dic);
                }

            }
            if (sheetName == "发文数据") dt_xhsCode = customer.GetXHSCode();
            
            //生成表
            npoiCell = new NPOICell(excelPath);
            if (!string.IsNullOrWhiteSpace(sheetName)) npoiCell.SetCurrentWorksheet(sheetName);

            List<Entity> entities = configs[configPath].Entities;//注意每个config中不一定只有一个实体，可以配置多个，这样每个excel可以导入到多张表中
            headerIndex = configs[configPath].HeaderIndex;
            rowcount = npoiCell.GetRowCount();
            foreach (Entity entity in entities)
            {
                ClearDataTable();
                InitLogDataTable();
                customerID_dt = customer.GetCustomerID();

                dataIndex = configs[configPath].DataIndex;
                string tablename = entity.Name;

                foreach (Property p in entity.Propertys)
                {
                    try
                    {
                        string columntype = p.DataType;
                        columntype = "System." + p.DataType;
                        if (columntype == "System.string") columntype = "System.String";
                        temp_dt.Columns.Add(p.ColumnName, Type.GetType(columntype));
                    }
                    catch (Exception aa)
                    {
                    }
                }

                try
                {
                    while (IsEnd(npoiCell, dataIndex))
                    {
                        if (entity.Propertys.Count > 0)//说明配置了Column字段
                            GetRow(npoiCell, entity, dataIndex, tablename);
                        dataIndex++;
                    }
                }
                catch (Exception ex)
                {

                }
                //将数据插入数据库
                string connectionString = XHD.DBUtility.PubConstant.ConnectionString;
                SqlConnection conn = new SqlConnection(connectionString);
                conn.Open();
                using (SqlBulkCopy sqlBC = new SqlBulkCopy(conn))
                {
                    sqlBC.BatchSize = 1000;
                    sqlBC.BulkCopyTimeout = 3600;
                    sqlBC.NotifyAfter = 10000;

                    sqlBC.DestinationTableName = entity.Name;
                    if (entity.Propertys.Count > 0)//说明配置了Column字段
                    {
                        foreach (Property p in entity.Propertys)//遍历实体的所有属性
                        {
                            sqlBC.ColumnMappings.Add(p.ColumnName, p.ColumnName);
                        }
                    }
                    try
                    {
                        sqlBC.WriteToServer(temp_dt);

                        sqlBC.DestinationTableName = "Sys_log";
                        sqlBC.ColumnMappings.Clear();
                        foreach (string f in log_fields) sqlBC.ColumnMappings.Add(f, f);
                        sqlBC.WriteToServer(log_dt);
                    }
                    catch (Exception ex)
                    {
                        Controller.IO.LogManager.Add(ex.ToString());
                    }
                }
            }
            return $"{{\"success\":{success},\"error\":{error},\"message\":\"{errortxt}\"}}";
        }
        private string ExistUid(string name)
        {
            string result = "";
            DataSet ds1 = customer.GetList($"[cus_name]='{name}' ");
            if (ds1.Tables[0].Rows.Count > 0)
            {
                result = "客户： " + ds1.Tables[0].Rows[0]["employee"].ToString();
            }
            return result;
        }
        private string ExistXHSCode(string xhs_code)
        {
            string result = "";
            DataSet ds1 = customer.GetList($"[xhs_code]='{xhs_code}' ");
            if (ds1.Tables[0].Rows.Count > 0)
            {
                result = "客户： " + ds1.Tables[0].Rows[0]["xhs_nickname"].ToString();
            }
            return result;
        }
        /// <summary>
        /// 得到查询值sql语句段
        /// </summary>
        /// <param name="npoiCell"></param>
        /// <param name="entity"></param>
        /// <param name="headerIndex"></param>
        /// <param name="dataIndex"></param>
        /// <returns></returns>
        private void GetRow(NPOICell npoiCell, Entity entity, int dataIndex, string tablename)
        {
            DataRow dr = temp_dt.NewRow();
            string v = "";
            string error_text = "";
            foreach (Property p in entity.Propertys)//遍历实体的所有属性
            {
                if (!string.IsNullOrEmpty(p.CodeKey))
                {
                    v = GetCodeTableValue(npoiCell, p, dataIndex);//注意如果单元格本身的值就有“'”的情况
                    if (v == "xhdresult:error")
                    {
                        error++;
                        errortxt += $"第【{dataIndex}】行【{p.HeaderText}】列在系统中找不到对应的值。";
                        error_text += $"第【{dataIndex}】行【{p.HeaderText}】列在系统中找不到对应的值。";
                        return;
                    }
                }
                else//说明此属性是一个代码表字段
                {
                    int columnindex = GetColumnIndexByHeaderText(npoiCell, p.HeaderText);
                    if (npoiCell.IsMerged(dataIndex, columnindex))//是否为合并单元格(对于合并单元格取此合并单元格的第一个值)
                    {
                        v = npoiCell.GetMergedCellValue(dataIndex, columnindex);
                    }
                    else
                    {
                        v = npoiCell.GetCellValue(dataIndex, columnindex);
                        #region 特殊字段的处理
                        #region 处理表 CRM_Customer
                        if (tablename.ToLower() == "CRM_Customer".ToLower())
                        {
                            if ((new List<string>() { "xhs_guanzhu", "xhs_fun", "xhs_collect", "xhs_notes" }).Contains(p.ColumnName))
                            {
                                if (!string.IsNullOrWhiteSpace(v))
                                {
                                    if (v.EndsWith("万"))
                                    {
                                        string t = v.Substring(0, v.Length - 1);
                                        if (!string.IsNullOrWhiteSpace(t)) v = (Convert.ToDecimal(t) * 10000).ToString("f0");
                                        else v = "";
                                    }
                                }
                            }
                            if ((new List<string>() { "xhs_offer", "xhs_tandian" }).Contains(p.ColumnName))
                            {
                                if (string.IsNullOrWhiteSpace(v)) v = "";
                            }
                            if (p.ColumnName == "xhs_isPartner")
                            {
                                if (v == "是") v = "1";
                                else v = "";
                            }
                            if (p.ColumnName == "xhs_code")
                            {
                                if (string.IsNullOrWhiteSpace(v)) v = "";
                                else
                                {
                                    int n = v.LastIndexOf('/');
                                    if (n < 0) v = "";
                                    else v = v.Substring(n + 1);
                                }
                                //if (string.IsNullOrWhiteSpace(v))
                                //{
                                //    error++;
                                //    errortxt += $"第【{dataIndex}】行【{p.HeaderText}】客户没有编码。";
                                //    return;
                                //}
                                if (ExistXHSCode(v).Length > 0 || temp_dt.Select("xhs_code='" + v + "'").Length > 0)
                                {
                                    error++;
                                    errortxt += "编码为 【" + v + "】 的会员已经存在！";
                                    return;
                                }
                            }
                        }
                        #endregion 处理表 CRM_Customer
                        #region 处理表 Sale_order_sheet
                        else if (tablename.ToLower() == "Sale_order_sheet".ToLower())
                        {
                            if ((new List<string>() { "xhs_fun", "xhs_praise" }).Contains(p.ColumnName))
                            {
                                if (!string.IsNullOrWhiteSpace(v))
                                {
                                    if (v.EndsWith("万"))
                                    {
                                        string t = v.Substring(0, v.Length - 1);
                                        if (!string.IsNullOrWhiteSpace(t)) v = (Convert.ToDecimal(t) * 10000).ToString("f0");
                                        else v = "";
                                    }
                                }
                            }
                            if (p.ColumnName.ToLower() == "xhs_code".ToLower() || p.ColumnName.ToLower() == "xhs_article_code".ToLower())
                            {
                                if (string.IsNullOrWhiteSpace(v)) v = "";
                                else
                                {
                                    int n = v.LastIndexOf('/');
                                    if (n < 0) v = "";
                                    else v = v.Substring(n + 1);
                                }
                                if (v.Length > 0 && v.IndexOf("?") >= 0) v = v.Substring(0, v.IndexOf("?"));
                            }
                            if ((new List<string>() { "xhs_gaofei" }).Contains(p.ColumnName))
                            {
                                if (string.IsNullOrWhiteSpace(v)) v = "";
                            }
                        }
                        #endregion 处理表 Sale_order_sheet
                        #region 处理表 Sale_order_implement
                        else if (tablename.ToLower() == "Sale_order_implement".ToLower())
                        {
                            if ((new List<string>() { "xhs_guangzhu", "xhs_fun", "xhs_hudong" }).Contains(p.ColumnName))
                            {
                                if (!string.IsNullOrWhiteSpace(v))
                                {
                                    if (v.EndsWith("万"))
                                    {
                                        string t = v.Substring(0, v.Length - 1);
                                        if (!string.IsNullOrWhiteSpace(t)) v = (Convert.ToDecimal(t) * 10000).ToString("f0");
                                        else v = "";
                                    }
                                }
                            }
                            if (p.ColumnName.ToLower() == "xhs_code".ToLower())
                            {
                                if (string.IsNullOrWhiteSpace(v)) v = "";
                                else
                                {
                                    int n = v.LastIndexOf('/');
                                    if (n < 0) v = "";
                                    else v = v.Substring(n + 1);
                                }
                            }
                            if (p.ColumnName.ToLower() == "xhs_proofread")
                            {
                                v = npoiCell.GetFormulaCellValue(dataIndex, columnindex, "N");
                            }
                        }
                        #endregion 处理表 Sale_order_implement
                        #region 处理表 Sale_order_article
                        else if (tablename.ToLower() == "Sale_order_article".ToLower())
                        {
                            if (p.ColumnName.ToLower() == "xhs_code".ToLower() || p.ColumnName.ToLower() == "xhs_article_code".ToLower())
                            {
                                if (string.IsNullOrWhiteSpace(v)) v = "";
                                else
                                {
                                    int n = v.LastIndexOf('/');
                                    if (n < 0) v = "";
                                    else v = v.Substring(n + 1);
                                }
                                if (v.Length > 0 && v.IndexOf("?") >= 0) v = v.Substring(0, v.IndexOf("?"));
                                if (p.ColumnName.ToLower() == "xhs_code".ToLower()) v = GetXHSCode(v);
                            }
                            if ((new List<string>() { "xhs_articlepraise", "xhs_articlecomment", "xhs_articlecollect", "xhs_notes", "xhs_fun", "xhs_praise" }).Contains(p.ColumnName))
                            {
                                if (!string.IsNullOrWhiteSpace(v))
                                {
                                    if (v.EndsWith("万"))
                                    {
                                        string t = v.Substring(0, v.Length - 1);
                                        if (!string.IsNullOrWhiteSpace(t)) v = (Convert.ToDecimal(t) * 10000).ToString("f0");
                                        else v = "";
                                    }
                                }
                            }
                        }
                        #endregion 处理表 Sale_order_article
                        #region 处理表 CRM_Customer_grow
                        else if (tablename.ToLower() == "CRM_Customer_grow".ToLower())
                        {
                            if (p.ColumnName.ToLower() == "xhs_code".ToLower())
                            {
                                if (string.IsNullOrWhiteSpace(v)) v = "";
                                else
                                {
                                    int n = v.LastIndexOf('/');
                                    if (n < 0) v = "";
                                    else v = v.Substring(n + 1);
                                }
                                if (v.Length > 0 && v.IndexOf("?") >= 0) v = v.Substring(0, v.IndexOf("?"));
                                v = GetXHSCode(v);
                            }
                            if ((new List<string>() { "xhs_guanzhu", "xhs_fun", "xhs_hudong", "xhs_notes" }).Contains(p.ColumnName))
                            {
                                if (!string.IsNullOrWhiteSpace(v))
                                {
                                    if (v.EndsWith("万"))
                                    {
                                        string t = v.Substring(0, v.Length - 1);
                                        if (!string.IsNullOrWhiteSpace(t)) v = (Convert.ToDecimal(t) * 10000).ToString("f0");
                                        else v = "";
                                    }
                                }
                            }
                        }
                        #endregion
                        #endregion
                    }
                    if (v == "")//说明单元格中没有任何值，就要考虑“默认值”和“必须”属性
                    {
                        if (GetDefaultValue(npoiCell, entity.Name, p.ColumnName, p.DefaultValue) != "")//说明有默认值
                        {
                            v = GetDefaultValue(npoiCell, entity.Name, p.ColumnName, p.DefaultValue);
                        }
                        else//如果单元格没有值并且无默认值，则检查此属性是否是必须的
                        {
                            if (!p.Required) v = "";
                            else
                            {
                                errortxt += $"列【{p.HeaderText}】不能为空。";
                                return;
                            }
                        }
                    }

                    //属性长度检查
                    if (p.DataLength != 0 && p.DataLength != 5000)
                    {
                        if (!ValidateDataLength(v, p.DataLength))
                        {
                            error++;
                            errortxt += $"列【{p.HeaderText}】中存在长度超过【{p.DataLength}】的数据。";
                            return;
                        }
                    }
                    //检查类型
                    if (p.DataType != "" && p.DataType != "string")
                    {
                        if (p.DataType == "DateTime")
                        {
                            try
                            {
                                if (!string.IsNullOrEmpty(v))
                                {
                                    System.IFormatProvider format = new System.Globalization.CultureInfo("zh-cn", true);
                                    DateTime dateTime = DateTime.Parse(v, format);
                                }
                            }
                            catch
                            {
                                error++;
                                errortxt += $"列【{p.HeaderText}】中存在非【{p.DataType}】类型数据。{v}";
                                error_text += $"列【{p.HeaderText}】中存在非【{p.DataType}】类型数据。{v}";
                            }
                        }
                    }

                }
                if (v == "") dr[p.ColumnName] = DBNull.Value;
                else dr[p.ColumnName] = v;
            }

            success++;
            temp_dt.Rows.Add(dr);


            string EventID = dr["id"].ToString();
            string log_content = $"记录号为【{EventID}】的客户数据导入成功！";
            if(tablename.ToString()== "sale_order_sheet") log_content = $"记录号为【{EventID}】的订单数据导入成功！";
            else if(tablename.ToString() == "Sale_order_article".ToLower()) log_content = $"记录号为【{EventID}】的发文数据导入成功！";
            else if(tablename.ToString() == "CRM_Customer_grow".ToLower()) log_content = $"记录号为【{EventID}】的成长数据导入成功！";

            if (!string.IsNullOrWhiteSpace(error_text))
            {
                log_content = $"记录号为【{EventID}】的订单数据导入失败！原因【{error_text}】";
                if (tablename.ToString() == "sale_order_sheet") log_content = $"记录号为【{EventID}】的订单数据导入失败！原因【{error_text}】";
                else if (tablename.ToString() == "Sale_order_article".ToLower()) log_content = $"记录号为【{EventID}】的发文数据导入失败！原因【{error_text}】";
                else if (tablename.ToString() == "CRM_Customer_grow".ToLower()) log_content = $"记录号为【{EventID}】的成长数据导入失败！原因【{error_text}】";

            }

            DataRow log_dr = log_dt.NewRow();
            log_dr["id"] = Guid.NewGuid().ToString();
            log_dr["EventType"] = tablename.ToLower() == "crm_customer" ? "客户导入" : (tablename.ToLower() == "sale_order_sheet" ? "订单导入" : (tablename.ToLower() == "sale_order_article" ? "发文数据导入" : "客户成长数据导入"));
            log_dr["EventID"] = getEventID(tablename, EventID, dr["xhs_code"].ToString());
            log_dr["EventTitle"] = string.Format("【{0}】客户数据导入", emp_name);
            log_dr["Log_Content"] = log_content;
            log_dr["UserID"] = emp_id;
            log_dr["UserName"] = emp_name;
            log_dr["IPStreet"] = context.Request.UserHostAddress;
            log_dr["EventDate"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

            log_dt.Rows.Add(log_dr);

        }
        private string getEventID(string tn, string eventID, string xhs_code)
        {
            if (tn.ToLower() == "crm_customer") return eventID;
            DataRow[] rows = customerID_dt.Select("xhs_code='" + xhs_code + "'");
            if (rows != null && rows.Length > 0) return rows[0]["id"].ToString();
            else return eventID;
        }
        private string GetXHSCode(string xhs_article_code)
        {
            if (string.IsNullOrWhiteSpace(xhs_article_code)) return "";
            if (dt_xhsCode == null) dt_xhsCode = customer.GetXHSCode();
            DataRow[] drs = dt_xhsCode.Select("xhs_article_code='" + xhs_article_code + "'");
            if (drs == null || drs.Length == 0) return "";
            return drs[0]["xhs_code"].ToString();
            //return customer.GetXHSCode(xhs_article_code);
        }
        private string GetCodeTableValue(JToken rowData, Property property, int dataIndex)
        {
            //int columnindex = GetColumnIndexByHeaderText(npoiCell, property.HeaderText);
            string value = rowData.Value<string>(property.ColumnName); //npoiCell.IsMerged(dataIndex, columnindex) ? npoiCell.GetMergedCellValue(dataIndex, columnindex) : npoiCell.GetCellValue(dataIndex, columnindex).Replace("'", "''");

            string key = null;

            if (property.CodeKey == "Provinces" || property.CodeKey == "City")
            {
                if (string.IsNullOrWhiteSpace(value)) return "";
                string sql = "select id from Sys_Param_" + property.CodeKey + " where CHARINDEX(" + property.CodeKey + ", '" + value + "',0)>0";
                key = DbHelperSQL.ReadValue(sql).ToString();
                return key;
            }
            if (string.IsNullOrEmpty(value)) return "";

            Dictionary<string, string> dic = ctcode[property.CodeKey];

            //string key = null;
            if (dic.ContainsKey(value)) key = dic[value].ToString();
            else key = "xhdresult:error";

            return key;
        }
        /// <summary>
        /// 得到代码表的对应值
        /// </summary>
        /// <param name="npoiCell"></param>
        /// <param name="property"></param>
        /// <param name="headerIndex"></param>
        /// <param name="dataIndex"></param>
        /// <returns></returns>
        private string GetCodeTableValue(NPOICell npoiCell, Property property, int dataIndex)
        {
            int columnindex = GetColumnIndexByHeaderText(npoiCell, property.HeaderText);
            string value = npoiCell.IsMerged(dataIndex, columnindex) ? npoiCell.GetMergedCellValue(dataIndex, columnindex) : npoiCell.GetCellValue(dataIndex, columnindex).Replace("'", "''");

            string key = null;

            if (property.CodeKey == "Provinces" || property.CodeKey == "City")
            {
                if (string.IsNullOrWhiteSpace(value)) return "";
                string sql = "select id from Sys_Param_" + property.CodeKey + " where CHARINDEX(" + property.CodeKey + ", '" + value + "',0)>0";
                key = DbHelperSQL.ReadValue(sql).ToString();
                return key;
            }
            if (string.IsNullOrEmpty(value))
                return "";

            Dictionary<string, string> dic = ctcode[property.CodeKey];

            //string key = null;
            if (dic.ContainsKey(value)) key = dic[value].ToString();
            else key = "xhdresult:error";

            return key;
        }
        /// <summary>
        /// 根据EndFlag标记判断当前数据行是否结束
        /// </summary>
        /// <param name="npoiCell"></param>
        /// <param name="endFlag"></param>
        /// <param name="row"></param>
        /// <returns></returns>
        private bool IsEnd(NPOICell npoiCell, int row)
        {
            int rowcount = npoiCell.GetRowCount();
            return row < rowcount;
        }

        /// <summary>
        /// 根据HeaderText配置节确定列索引（从0开始）
        /// </summary>
        /// <param name="npoiCell"></param>
        /// <param name="headerIndex"></param>
        /// <param name="headerText"></param>
        /// <returns></returns>
        private int GetColumnIndexByHeaderText(NPOICell npoiCell, string headerText)
        {
            int columnIndex = npoiCell.GetColumnCount();
            int r = -1;

            for (int i = 0; i < columnIndex; i++)
            {
                if (npoiCell.GetCellValue(headerIndex, i) == headerText)
                {
                    r = i;
                }
            }

            return r;
        }

        /// <summary>
        /// 根据DefaultValue配置节确定默认值
        /// </summary>
        /// <param name="npoiCell"></param>
        /// <param name="entityName"></param>
        /// <param name="columnName"></param>
        /// <param name="defaultValue"></param>
        /// <returns></returns>
        private string GetDefaultValue(NPOICell npoiCell, string entityName, string columnName, string defaultValue)
        {
            string r = "";
            switch (defaultValue)
            {
                case "NewID": r = this.NewID.ToString(); break;
                case "NewGuid": r = Guid.NewGuid().ToString(); break;
                case "NewTime": r = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"); break;
                default: r = defaultValue; break;
            }
            return r;
        }
        private string GetDefaultValue(string entityName, string columnName, string defaultValue)
        {
            string r = "";
            switch (defaultValue)
            {
                case "NewID": r = this.NewID.ToString(); break;
                case "NewGuid": r = Guid.NewGuid().ToString(); break;
                case "NewTime": r = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"); break;
                default: r = defaultValue; break;
            }
            return r;
        }

        /// <summary>
        /// 得到查询字段sql语句段
        /// </summary>
        /// <param name="npoiCell"></param>
        /// <param name="entity"></param>
        /// <returns></returns>
        private string GetSqlFieldString(NPOICell npoiCell, Entity entity)
        {
            string sql = "";
            foreach (Property p in entity.Propertys)
            {
                sql += p.ColumnName + ",";
            }
            sql = sql.TrimEnd(',');
            return sql;
        }

        //数据类型校验
        private bool ValidateDataType(string value, string type)
        {
            bool r = false;

            switch (type.ToLower())
            {
                case "number":
                    double n = 0;
                    r = double.TryParse(value, out n);
                    break;
                case "DateTime":
                    try
                    {
                        if (!string.IsNullOrEmpty(value))
                        {
                            System.IFormatProvider format = new System.Globalization.CultureInfo("zh-cn", true);
                            DateTime dateTime = DateTime.Parse(value, format);
                            return true;
                        }
                        r = false;
                    }
                    catch
                    {
                        r = false;
                    }
                    break;
                case "string":
                    r = true;
                    break;
                default: break; ;
            }
            return r;
        }

        //数据长度校验
        private bool ValidateDataLength(string value, int length)
        {
            if (value.Length > length)
            {
                return false;
            }
            else
            {
                return true;
            }
        }



        //初始化，主要将excel文件和配置类对应关系存放到configs对象中，方便以后遍历
        private void InitConfig()
        {
            configs = new Dictionary<string, Config>();
            List<string> excelFullNames = new List<string>();
            if (Directory.Exists(configPath))//判断目录是否存在(注意：除了多套excel对应多套模板，还可能有一个模板对应多个excel的情况）
            {
                FileHelper.InitFileInfoList();
                List<string> excelConfigFileFullNames = FileHelper.GetFileInfo(".xml", ConfigHelper.Instance().ExcelPath, true, true);//在目录中查找所有名称中包含".xml"的文件
                if (excelConfigFileFullNames.Count == 1)//说明是一个excel对应一个xml配置文件的情况
                {
                    AddConfigsByXmlFullNameHasNothingToExcelname(excelConfigFileFullNames[0]);
                }
                else if (excelConfigFileFullNames.Count > 1)//说明目录中有多个xml文件，对应多个excel
                {
                    foreach (string excelConfigFileFullName in excelConfigFileFullNames)
                    {
                        AddConfigsByXmlFullName(excelConfigFileFullName);
                    }
                }
                else
                {
                    throw new Exception("所指定目录不包含任何XLM模板，请重新指定！");
                }

            }
            else//说明指定的不是目录而是excel文件路径
            {
                AddConfigByExcelFullName(configPath);
            }

        }

        //根据Excel全路径构造路径和配置类对应关系（主要用于指导Excel全路径的情况）
        private void AddConfigByExcelFullName(string excelFullName)
        {
            string excelConfigFileFullName = excelFullName.Substring(0, excelFullName.LastIndexOf("\\")) + "\\" + Path.GetFileNameWithoutExtension(excelFullName) + ".xml";
            Config config = null;
            if (File.Exists(excelConfigFileFullName))
            {
                config = new Config(excelConfigFileFullName);//创建配置（Config）对象
                configs.Add(excelFullName, config);
            }
            else
            {
                throw new Exception("所指定文件没有对应的配置文件，请重新指定！" + excelConfigFileFullName);
            }
        }

        //根据XML文件全路径构造路径和配置类对应关系(得到一个xml文件对应的所有excel然后构造config对象存放到configs中)
        private void AddConfigsByXmlFullName(string xmlFullName)
        {
            string excelDirectory = xmlFullName.Substring(0, xmlFullName.LastIndexOf("\\"));//根据路径取得对应的目录
            Config config = null;
            FileHelper.InitFileInfoList();
            List<string> excelFullNames = FileHelper.GetFileInfo(Path.GetFileNameWithoutExtension(xmlFullName), excelDirectory, true, true);//根据xml的名称搜索包含此名称的文件
            if (excelFullNames.Count >= 1)
            {
                foreach (string excelFullName in excelFullNames)
                {
                    if (excelFullNames.IndexOf(".xls") != -1)//必须是excel文件(排除xml文件)
                    {
                        config = new Config(xmlFullName);
                        configs.Add(excelFullName, config);
                    }
                }
            }
            else
            {
                throw new Exception("所指定模板不包含对应的Excel文件，请重新指定！");
            }
        }

        //根据XML文件全路径构造路径和配置类对应关系（此种情况由于只有一个xml，必然对应一个或多个excel文件，所以只需要查找excel文件即可）
        private void AddConfigsByXmlFullNameHasNothingToExcelname(string xmlFullName)
        {
            Config config = null;
            string excelDirectory = xmlFullName.Substring(0, xmlFullName.LastIndexOf("\\"));
            FileHelper.InitFileInfoList();
            List<string> excelFullNames = FileHelper.GetFileInfo(".xls", excelDirectory, true, true);
            if (excelFullNames.Count >= 1)
            {
                foreach (string excelFullName in excelFullNames)
                {
                    config = new Config(xmlFullName);
                    configs.Add(excelFullName, config);
                }
            }
            else
            {
                throw new Exception("所指定模板不包含对应的Excel文件，请重新指定！");
            }
        }
    }

    public class ctlist
    {
        public string ctkey;
        public string cttext;

        public ctlist(string text, string value)
        {
            this.CtKey = value;
            this.CtText = text;
        }

        public string CtKey
        {
            get { return ctkey; }
            set { ctkey = value; }
        }

        public string CtText
        {
            get { return cttext; }
            set { cttext = value; }
        }
    }
}
